package cn.bluemobi.platform.utils.steel;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class SteelTypeImportUtils {
    public static void main(String[] args) throws UnsupportedEncodingException, FileNotFoundException {
        Map<SteelSepcification, SteelType> map = new LinkedHashMap<SteelSepcification, SteelType>();
//        processXls(map, "C:\\Users\\chenj02\\Desktop\\钢种型号资料\\CHINA_453-455-1.xls", "cn");
//        processXls(map, "C:\\Users\\chenj02\\Desktop\\钢种型号资料\\CHINA_456-457-1.xls", "cn");
//        processXls(map, "C:\\Users\\chenj02\\Desktop\\钢种型号资料\\EU_582-601-1.xls", "eu");
//        processXls(map, "C:\\Users\\chenj02\\Desktop\\钢种型号资料\\COMM.xls", "comm");
//        processXls(map, "C:\\Users\\chenj02\\Desktop\\钢种型号资料\\JP_349-1.xls", "jp");
//        processXls(map, "C:\\Users\\chenj02\\Desktop\\钢种型号资料\\JP_499-506-1.xls", "jp");
//        processXls(map, "C:\\Users\\chenj02\\Desktop\\钢种型号资料\\USA_272-289-1.xls", "usa");
        processXls(map, "C:\\Users\\chenj02\\Desktop\\钢种型号资料\\USA_532-541-1.xls", "usa");
        Collection<SteelType> types = map.values();
        int idStart = 3626;// 数据库id起始位置
        String fileName = "C:\\Users\\chenj02\\Desktop\\钢种型号资料\\init.sql";
        try {
            toSqlFile(types, idStart, fileName);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 生成脚本
     */
    private static void toSqlFile(Collection<SteelType> types, int idStart, String fileName) throws IOException {
        OutputStreamWriter writer = null;
        try {
            writer = new OutputStreamWriter(new FileOutputStream(fileName), "utf-8");
            int id = idStart;
            for (SteelType type : types) {
                String insertType = generateTypeSql(type, id);
                List<String> insertDetail;
                insertDetail = generateDetailSql(type, id);
                id++;

                writer.append(insertType + "\n");
                for (String detail : insertDetail) {
                    writer.append(detail + "\n");
                }
                writer.write("\n");
            }
        } finally {
            writer.close();
        }
    }

    private static List<String> generateDetailSql(SteelType type, int id) {
        List<String> list = new ArrayList<String>();
        SteelSepcification spe = type.getSpec();
        list.add(generateDetailSql(id, "C", spe.getcMin(), spe.getcMax()));
        list.add(generateDetailSql(id, "Si", spe.getSiMin(), spe.getSiMax()));
        list.add(generateDetailSql(id, "Mn", spe.getMnMin(), spe.getMnMax()));
        list.add(generateDetailSql(id, "P", spe.getpMin(), spe.getpMax()));
        list.add(generateDetailSql(id, "S", spe.getsMin(), spe.getsMax()));
        list.add(generateDetailSql(id, "Cr", spe.getCrMin(), spe.getCrMax()));
        list.add(generateDetailSql(id, "Ni", spe.getNiMin(), spe.getNiMax()));
        list.add(generateDetailSql(id, "Mo", spe.getMoMin(), spe.getMoMax()));
        list.add(generateDetailSql(id, "Cu", spe.getCuMin(), spe.getCuMax()));
        list.add(generateDetailSql(id, "N", spe.getnMin(), spe.getnMax()));
        list.add(generateDetailSql(id, "Ti", spe.getTiMin(), spe.getTiMax()));
        list.add(generateDetailSql(id, "Nb", spe.getNbMin(), spe.getNbMax()));
        list.add(generateDetailSql(id, "Al", spe.getA1Min(), spe.getA1Max()));
        list.add(generateDetailSql(id, "Fe", spe.getFeMin(), spe.getFeMax()));
        list.add(generateDetailSql(id, "W", spe.getwMin(), spe.getwMax()));
        list.add(generateDetailSql(id, "B", spe.getbMin(), spe.getbMax()));
        list.add(generateDetailSql(id, "Co", spe.getCoMin(), spe.getCoMax()));
        list.add(generateDetailSql(id, "V", spe.getvMin(), spe.getvMax()));
        return list;
    }

    private static String generateDetailSql(int id, String propName, String min, String max) {
    	if(min!=null || max != null){
    		StringBuilder sb = new StringBuilder(
                    "INSERT INTO app_steel_detail_item (state,create_time,steel_id,elements_name,`max_value`,min_value) VALUES ('ADD',now(),"
                            + id + ",");
            sb.append("'").append(propName).append("',");
            if (max != null) {
                sb.append("'").append(max).append("',");
            } else {
                sb.append("null,");
            }
            if (min != null) {
                sb.append("'").append(min).append("'");
            } else {
                sb.append("null");
            }
            sb.append(");");
            return sb.toString();
    	}
		return "";
        
    }

    private static String generateTypeSql(SteelType type, int id) {
        StringBuilder sb = new StringBuilder(
                "INSERT INTO app_steel_type (id,state,create_time,us,china_gb,eu,jp,comm) VALUES (");
        sb.append(id).append(",'ADD',now(),");
        if (type.getUsa().isEmpty()) {
            sb.append("null,");
        } else {
            sb.append("'").append(StringUtils.join(type.getUsa(), ",")).append("',");
        }
        if (type.getCn().isEmpty()) {
            sb.append("null,");
        } else {
            sb.append("'").append(StringUtils.join(type.getCn(), ",")).append("',");
        }
        if (type.getEu().isEmpty()) {
            sb.append("null,");
        } else {
            sb.append("'").append(StringUtils.join(type.getEu(), ",")).append("',");
        }
        if (type.getJp().isEmpty()) {
        	 sb.append("null,");
        } else {
            sb.append("'").append(StringUtils.join(type.getJp(), ",")).append("';");
        }
        if (type.getComm().isEmpty()) {
            sb.append("null);");
        } else {
            sb.append("'").append(StringUtils.join(type.getComm(), ",")).append("');");
        }
        return sb.toString();
    }

    /**
     * 读成钢类型数据
     */
    private static void processXls(Map<SteelSepcification, SteelType> map, String file, String country) {
        HSSFWorkbook wb = null;
        try {
            wb = new HSSFWorkbook(new FileInputStream(file));
        } catch (Exception e) {
            System.out.println("excel文件无法读取，请检查文件是否正常");
            System.exit(1);
        }
        HSSFSheet sheet = wb.getSheetAt(0);
        int totalRows = sheet.getLastRowNum() + 1;// 总记录数
        for (int i = 2; i < totalRows; i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<String> names = getSteelName(row);
            SteelSepcification spe = getSepcification(row);
            SteelType type = null;
            if (map.containsKey(spe)) {
                type = map.get(spe);
            } else {
                type = new SteelType();
                type.setSpec(spe);
                map.put(spe, type);
            }
            if ("cn".equals(country)) {
                type.getCn().addAll(names);
            } else if ("eu".equals(country)) {
                type.getEu().addAll(names);
            } else if ("jp".equals(country)) {
                type.getJp().addAll(names);
            } else if ("usa".equals(country)) {
                type.getUsa().addAll(names);
            } else if ("comm".equals(country)) {
                type.getComm().addAll(names);
            }
        }
    }

    /**
     * 获取所有指标
     * 
     * @param row
     * @return
     */
    private static SteelSepcification getSepcification(HSSFRow row) {
        SteelSepcification s = new SteelSepcification();
        s.setcMin(g(1, row));
        s.setcMax(g(2, row));
        s.setSiMin(g(3, row));
        s.setSiMax(g(4, row));
        s.setMnMin(g(5, row));
        s.setMnMax(g(6, row));
        s.setpMin(g(7, row));
        s.setpMax(g(8, row));
        s.setsMin(g(9, row));
        s.setsMax(g(10, row));
        s.setCrMin(g(11, row));
        s.setCrMax(g(12, row));
        s.setNiMin(g(13, row));
        s.setNiMax(g(14, row));
        s.setMoMin(g(15, row));
        s.setMoMax(g(16, row));
        s.setCuMin(g(17, row));
        s.setCuMax(g(18, row));
        s.setnMin(g(19, row));
        s.setnMax(g(20, row));
        s.setTiMin(g(21, row));
        s.setTiMax(g(22, row));
        s.setNbMin(g(23, row));
        s.setNbMax(g(24, row));
        s.setA1Min(g(25, row));
        s.setA1Max(g(26, row));
        s.setFeMin(g(27, row));
        s.setFeMax(g(28, row));
        s.setwMin(g(29, row));
        s.setwMax(g(30, row));
        s.setbMin(g(31, row));
        s.setbMax(g(32, row));
        s.setCoMin(g(33, row));
        s.setCoMax(g(34, row));
        s.setvMin(g(35, row));
        s.setvMax(g(36, row));
        return s;
    }

    private static String g(int i, HSSFRow row) {
        return getValue(row.getCell(i));
    }

    /**
     * 第一列 钢种名
     * 
     * @param row
     * @return
     */
    private static List<String> getSteelName(HSSFRow row) {
        String str = getValue(row.getCell(0));
        if (StringUtils.isNotBlank(str)) {
            return Arrays.asList(str.split(","));
        }
        return new ArrayList<String>();
    }

    private static String getValue(HSSFCell cell) {
        if (cell == null) {
            return null;
        }
        if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        } else {
            return cell.getStringCellValue();
        }
    }
}
